package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.dto.User;
import utility.Database;
import utility.Log;

public class UserModel extends Database {
	public boolean insert(User dto) {
		String sql = "insert into tbl_user(user_id,user_name,user_gender,user_phone,user_email,user_password,user_image,user_type,favourite_club_id) values(null,?,?,?,?,?,?,?,?)";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getGender());
			pstmt.setString(3, dto.getPhone());
			pstmt.setString(4, dto.getEmail());
			pstmt.setString(5, dto.getPassword());
			pstmt.setString(6, dto.getImage());
			pstmt.setString(7, dto.getType());
			pstmt.setInt(8, dto.getFavouriteClubId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean delete(int user_id) {
		String sql = "delete from tbl_user where user_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user_id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean update(User dto) {
		String sql = "update tbl_user set user_name=?,user_gender=?,user_phone=?,user_email=?,user_password=?,user_image=?,user_type=?,favourite_club_id=? where user_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getGender());
			pstmt.setString(3, dto.getPhone());
			pstmt.setString(4, dto.getEmail());
			pstmt.setString(5, dto.getPassword());
			pstmt.setString(6, dto.getImage());
			pstmt.setString(7, dto.getType());
			pstmt.setInt(8, dto.getFavouriteClubId());
			pstmt.setInt(9, dto.getId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public User get(int user_id) {
		String sql = "select * from tbl_user where user_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				User dto = new User();
				dto.setId(user_id);
				dto.setName(rs.getString("country_name"));
				dto.setGender(rs.getString("user_gender"));
				dto.setPhone(rs.getString("user_phone"));
				dto.setEmail(rs.getString("user_email"));
				dto.setPassword(rs.getString("user_password"));
				dto.setType(rs.getString("user_type"));
				dto.setFavouriteClubId(rs.getInt("favourite_club_id"));
				dto.setImage(rs.getString("user_image"));
				return dto;
			}
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(String search, int limit, int page) {
		String sql = "select * from tbl_user where user_name like ? ORDER BY user_id DESC limit ?,?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			pstmt.setInt(2, (page - 1) * limit);
			pstmt.setInt(3, limit);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(int limit, int page) {
		return list("", limit, page);
	}

	public int count(String search) {
		String sql = "select count(*) from tbl_user where user_name like ?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			return rs.next() ? rs.getInt(1) : 0;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return 0;
	}

	public int count() {
		return count("");
	}

}
